-- oe producers
CREATE TABLE import_filtron_oe_producers (
	import_producer_id int IDENTITY(1,1) NOT NULL,
	producer_created datetime NOT NULL,
	producer_modified datetime NOT NULL,
	producer_id int NOT NULL,
	producer_name nvarchar(255) NOT NULL,
	producer_oe nvarchar(20) NULL,
PRIMARY KEY CLUSTERED 
(
	import_producer_id ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
) ON PRIMARY
GO

ALTER TABLE import_filtron_oe_producers ADD  DEFAULT (getdate()) FOR producer_created
GO

ALTER TABLE import_filtron_oe_producers ADD  DEFAULT (getdate()) FOR producer_modified
GO

CREATE UNIQUE INDEX import_filtron_oe_producers_idx0 ON import_filtron_oe_producers (producer_name);
GO

CREATE TRIGGER import_filtron_oe_producers_fu ON import_filtron_oe_producers  
FOR UPDATE
AS
BEGIN
  UPDATE import_filtron_oe_producers 
		SET producer_modified = GETDATE() 
		WHERE import_producer_id IN (SELECT import_producer_id FROM inserted);
END

-- info logistyczne
CREATE TABLE import_filtron_logistics_info (
	import_logistics_info_id int IDENTITY(1,1) NOT NULL,
	logistics_info_created datetime NOT NULL,
	logistics_info_modified datetime NOT NULL,
	filter_number nvarchar(255) NOT NULL,
	hd nvarchar(80) NULL,
	min_ilosc_zam int NULL,
	ilosc_zg int NULL,
	ilosc_pal int NULL,
	kart_w int NULL,
	kart_h int NULL,
	kart_l int NULL,
	waga nvarchar(80) NULL,
	kod_ean nvarchar(80) NULL,
PRIMARY KEY CLUSTERED 
(
	import_logistics_info_id ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
) ON PRIMARY
GO

CREATE UNIQUE INDEX import_filtron_logistics_info_idx0 ON import_filtron_logistics_info (filter_number);
GO

ALTER TABLE import_filtron_logistics_info ADD  DEFAULT (getdate()) FOR logistics_info_created
GO

ALTER TABLE import_filtron_logistics_info ADD  DEFAULT (getdate()) FOR logistics_info_modified
GO

CREATE TRIGGER import_filtron_logistics_info_fu ON import_filtron_logistics_info  
FOR UPDATE
AS
BEGIN
  UPDATE import_filtron_logistics_info 
		SET logistics_info_modified = GETDATE() 
		WHERE import_logistics_info_id IN (SELECT import_logistics_info_id FROM inserted);
END

-- docelowe oe
ALTER TABLE filtron_interchanges ADD filter_is_oe BIT;
GO

ALTER TABLE filtron_interchanges ADD DEFAULT 0 FOR filter_is_oe
GO


-- docelowe info logistics
-- filtron
CREATE TABLE filtron_logistics_info (
	logistics_info_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	logistics_info_created datetime NOT NULL,
	logistics_info_modified datetime NOT NULL,
	filter_id INT NOT NULL,
	hd nvarchar(80) NULL,
	min_ilosc_zam int NULL,
	ilosc_zg int NULL,
	ilosc_pal int NULL,
	kart_w int NULL,
	kart_h int NULL,
	kart_l int NULL,
	waga nvarchar(80) NULL,
	kod_ean nvarchar(80) NULL
)
GO

ALTER TABLE filtron_logistics_info  WITH CHECK ADD CONSTRAINT filtron_logistics_info_fk0 FOREIGN KEY(filter_id)
REFERENCES filtron_filters (filter_id)
GO

ALTER TABLE filtron_logistics_info CHECK CONSTRAINT filtron_logistics_info_fk0
GO

ALTER TABLE filtron_logistics_info ADD  DEFAULT (getdate()) FOR logistics_info_created
GO

ALTER TABLE filtron_logistics_info ADD  DEFAULT (getdate()) FOR logistics_info_modified
GO

CREATE TRIGGER filtron_logistics_info_fu ON filtron_logistics_info  
FOR UPDATE
AS
BEGIN
  UPDATE filtron_logistics_info 
		SET logistics_info_modified = GETDATE() 
		WHERE logistics_info_id IN (SELECT logistics_info_id FROM inserted);
END


-- wix
CREATE TABLE wix_logistics_info (
	logistics_info_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	logistics_info_created datetime NOT NULL,
	logistics_info_modified datetime NOT NULL,
	filter_id INT NOT NULL,
	hd nvarchar(80) NULL,
	min_ilosc_zam int NULL,
	ilosc_zg int NULL,
	ilosc_pal int NULL,
	kart_w int NULL,
	kart_h int NULL,
	kart_l int NULL,
	waga nvarchar(80) NULL,
	kod_ean nvarchar(80) NULL)
GO

ALTER TABLE wix_logistics_info  WITH CHECK ADD CONSTRAINT wix_logistics_info_fk0 FOREIGN KEY(filter_id)
REFERENCES wix_filters (filter_id)
GO

ALTER TABLE wix_logistics_info CHECK CONSTRAINT wix_logistics_info_fk0
GO

ALTER TABLE wix_logistics_info ADD  DEFAULT (getdate()) FOR logistics_info_created
GO

ALTER TABLE wix_logistics_info ADD  DEFAULT (getdate()) FOR logistics_info_modified
GO

CREATE TRIGGER wix_logistics_info_fu ON wix_logistics_info  
FOR UPDATE
AS
BEGIN
  UPDATE wix_logistics_info 
		SET logistics_info_modified = GETDATE() 
		WHERE logistics_info_id IN (SELECT logistics_info_id FROM inserted);
END
